package ie.nolantech.gpsg;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.zip.GZIPOutputStream;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class GpsHelper extends SQLiteOpenHelper {

	protected static final String TAG = "HN.GpsHelper";

	private static final String DATABASE_NAME = "gpsgame.db";
	private static final int SCHEMA_VERSION = 4;

	private static final String create_loc_log = "CREATE TABLE LOC_LOG(_id INTEGER PRIMARY KEY AUTOINCREMENT, lat, lon, speed, date_done default(DATETIME('NOW')), value_type)";

	public GpsHelper(Context context) {
		super(context, DATABASE_NAME, null, SCHEMA_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(create_loc_log);

	}

	public String exportData(Context ctx) {
		String fileName = "loc_log.csv";
		File file = new File(ctx.getExternalFilesDir(null), fileName);
		// fileName = file.getAbsolutePath();

		FileOutputStream fos = null;

		StringBuffer forReturn = new StringBuffer();

		Cursor c = this.getAllLoc();
		try {
			if (c.moveToFirst()) {
				fos = new FileOutputStream(file);
				do {
					int _id = c.getInt(c.getColumnIndex("_id"));
					String lat = c.getString(c.getColumnIndex("lat"));
					String lon = c.getString(c.getColumnIndex("lon"));
					String dateDone = c
							.getString(c.getColumnIndex("date_done"));
					String toWrite = "(" + _id + ", " + lat + ", " + lon + ", "
							+ dateDone + ")\n";
					fos.write(toWrite.getBytes());

					forReturn.append(toWrite);
				} while (c.moveToNext());
			} else {
				Log.d(TAG, "There were no values in the cursor.");
			}

		} catch (IOException e) {
			// Unable to create file, likely because external storage is
			// not currently mounted.
			Log.w("ExternalStorage", "Error writing " + fileName, e);
		} finally {
			c.close();
			try {
				if (fos != null) {
					fos.close();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}
		}

		// zip bit
		GZIPOutputStream zos = null;
		try {
			OutputStream os = new FileOutputStream(file);

			byte[] bytes = forReturn.toString().getBytes();
			zos = new GZIPOutputStream(new BufferedOutputStream(os));
			zos.write(bytes);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				zos.finish();
				zos.close();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}

		return forReturn.toString();
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.d(TAG, "on upgrade called. Old version:" + oldVersion
				+ ". New version:" + newVersion);
		if (oldVersion == 1) {
			db.execSQL("create table loc_log_temp as select * from loc_log;");
			db.execSQL("drop table loc_log;");
			db.execSQL(create_loc_log);
			db.execSQL("insert into loc_log (_id, lat, lon, date_done) select _id, lat, lon, datetime('now') from loc_log_temp");
			db.execSQL("drop table loc_log_temp;");
		}
		if (oldVersion <= 2) {
			db.execSQL("create table loc_log_temp as select * from loc_log;");
			db.execSQL("drop table loc_log;");
			db.execSQL(create_loc_log);
			db.execSQL("insert into loc_log (_id, lat, lon, date_done, value_type) select _id, lat, lon, datetime('now'), 'UNKNOWN' from loc_log_temp");
		}

		if (oldVersion <= 4) {
			db.execSQL("create table loc_log_temp as select * from loc_log;");
			db.execSQL("drop table loc_log;");
			db.execSQL(create_loc_log);
			db.execSQL("insert into loc_log (_id, lat, lon, speed, date_done, value_type) select _id, lat, lon, -1, datetime('now'), 'UNKNOWN' from loc_log_temp");
		}

	}

	public void insertPosition(double lat, double lon, float speed,
			String valueType) {
		ContentValues cv = new ContentValues();

		cv.put("lat", lat);
		cv.put("lon", lon);
		cv.put("speed", speed);
		cv.put("value_type", valueType);

		getWritableDatabase().insert("LOC_LOG", "lat", cv);
		close();
	}

	public void deletePoint(int _id) {
		getWritableDatabase().delete("loc_log", "_id=?",
				new String[] { "" + _id });
		close();
	}

	public void deleteAllLocations() {
		getWritableDatabase().execSQL("delete from loc_log");
		close();
	}

	public Cursor getAllLoc() {
		return getAllLoc(-1);
	}

	public Cursor getAllLoc(int limit) {
		return (getReadableDatabase()
				.rawQuery(
						"SELECT _id, lat, lon, date_done, value_type FROM LOC_LOG ORDER BY date_done desc limit "
								+ limit, null));
	}

	public Cursor getEntriesOverGoal(int minSpeed) {
		return (getReadableDatabase().rawQuery(
				"SELECT _id, lat, lon, date_done, value_type, speed FROM LOC_LOG where speed >"
						+ minSpeed + " ORDER BY speed desc ", null));
	}

	@Override
	public void onOpen(SQLiteDatabase db) {
		super.onOpen(db);

		Log.d("PRAGMA:", checkForeignKeySupport(db));

		db.rawQuery("pragma foreign_keys=1", null).close();

		Log.d("PRAGMA:", checkForeignKeySupport(db));
		Log.d("init-log:database-name", DATABASE_NAME);

	}

	private String checkForeignKeySupport(SQLiteDatabase db) {
		Cursor c = db.rawQuery("pragma foreign_keys", null);
		if (c != null) {
			c.moveToFirst();
			String retVal = c.getString(0);
			c.close();
			return ("1".equals(retVal)) ? "Foreign key support is ON"
					: "Foreign key support is OFF";
		}
		return "Problem with foreign key check";
	}

}
